﻿
CREATE DATABASE TUYENDUNG

GO
 
USE TUYENDUNG

GO
CREATE TABLE THONGTINTD (
	STT  int identity (1,1),
	Ngay_yeu_cau datetime not null,
	Vi_tri nvarchar (30) constraint PK_THONGTINTD primary key not null,
	So_luong int not null,
	Ngay_het_han datetime not null,
	Yeu_cau nvarchar(50) not null,
	Mo_ta nvarchar(500) not null,
	Luong_khoi_diem nvarchar(10) not null, 
	Noi_lam_viec nvarchar(30) not null,
	Ghi_chu nvarchar (500)
	);
INSERT INTO THONGTINTD VALUES ('11/01/2013', N'BẢO VỆ','2', '01/01/2014',N'CÓ TRÁCH NHIỆM',N'LÀM 8H TRÊN NGÀY',N'COI XE VÀ BẢO VỆ',N'2 TRIỆU',N'CHI NHÁNH HN','');
GO

CREATE TABLE UNGVIEN(
	Ma_UVien varchar not null,
	So_CMND varchar(11) not null,  
	Ho_ten nvarchar(50) not null, 
	Ngay_Sinh datetime not null ,
	Gioi_tinh bit not null,
	Noi_sinh nvarchar(50) not null,
	Quoc_tich nvarchar(50) not null,
	Dia_chi_lien_lac nvarchar(50) not null, 
	Dia_chi_thuong_tru nvarchar(50) not null ,
	Dien_thoai_nha varchar(20) not null,
	Dien_thoai_di_dong varchar(20) not null, 
	Email varchar(50) not null,
	Tinh_trang_hon_nhan nvarchar(20),
	Chieu_cao varchar(10) not null,
	Trinh_do_hoc_van nvarchar(20) not null,
	Cac_khoa_huan_luyen nvarchar(50) not null, 
	Kinh_nghiem_lam_viec nvarchar(50) not null,
	Vi_tri_tuyen_dung nvarchar(30) constraint FK_VITRI foreign key references THONGTINTD(Vi_tri) not null,
	Anh image not null,
	constraint PK_UNGVIEN  primary key (Ma_UVien)
);

GO

--INSERT INTO UNGVIEN VALUES ('UV1', N'Chử Kim Mười', '08/10/1992', '1', N'Hải Dương', N'Việt Nam', N'55, Giải Phóng',
--N'Cầu Giấy', '3857498', '01673672505', 'chukimmuoi@gmail.com', N'Đôc Thân', '170cm', N'Đại Học', 'IT', N'Không ra gì',N'CODER', '');
--GO

--SELECT * FROM UNGVIEN;	

--SELECT Ho_ten, Gioi_tinh, convert(varchar(11),Ngay_Sinh,103), Anh FROM UNGVIEN;

--DELETE UNGVIEN WHERE Ho_ten = N'' AND So_CMND = '';
--SELECT Ma_UVien, Ho_ten, Gioi_tinh, convert(varchar(11),Ngay_Sinh,103), Anh FROM UNGVIEN WHERE Ma_UVien = '3' OR Ho_ten = N'Đỗ Thị Sim';
GO

CREATE TABLE HOSO (
	Ma_ho_so int identity (1,1) constraint PK_HOSO primary key,
	Ma_Ung_Vien varchar constraint FK_MaUngVien foreign key references UNGVIEN(Ma_UVien),
	Ngay_nop_ho_so datetime not null,
	Vi_tri nvarchar(30) not null
	);

GO

CREATE TABLE  PHONGBAN (
	STT int identity(1,1),
	Ma_Phong varchar(10) constraint PK_PHONGBAN primary key,
	Ten_Phong nvarchar(20) not null
);
GO
CREATE TABLE NHANVIEN(
	Ma_NV varchar(10) constraint PK_NHANVIEN primary key,
	Ho_ten nvarchar(30) not null,
	Chuc_vu nvarchar(15) not null,
	Ma_Phong varchar(10) constraint FK_Ma_Phong foreign key references PHONGBAN(Ma_Phong),
	Anh image not null
	);

GO
CREATE TABLE THONGBAO(
	Ma_TB int constraint PK_THONGBAO primary key,
	Ma_NV_lap_TB varchar(10)  constraint FK_Manhanvienlap foreign key references NHANVIEN(Ma_NV),
	Ngay_lap datetime not null,
	Noi_Dung text not null
	); 

	
GO
CREATE TABLE PHANQUYEN( 
	STT int identity (1,1),
	Ten_quyen nvarchar (50) primary key not null
	);

GO
CREATE TABLE NGUOISUDUNG(
	STT int identity(1,1),
	Ten_dang_nhap nvarchar(30) constraint PK_NGUOISUDUNG primary key not null ,
	Mat_khau varchar(30) not null,
	Ho_ten nvarchar(30) not null,
	Ngay_Sinh datetime not null ,
	Gioi_tinh bit not null,
	Anh image,
	Ma_NV varchar(10) constraint FK_NHANVIEN foreign key references NHANVIEN(Ma_NV),
	Ten_quyen nvarchar(50) constraint FK_NGUOISUDUNG foreign key references PHANQUYEN(Ten_quyen),
	Ngay_bat_dau_su_dung datetime
	);

GO
CREATE TABLE NHANVIENMOI(
	Ma_NV varchar(10) constraint PK_NHANVIENMOI primary key ,
	So_CMND int,
	Ho_ten nvarchar(30) not null,
	Vi_tri_tuyen_dung nvarchar (30) not null
	);
	